package com.rensanning.poi;

import java.io.File;
import java.io.FileInputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFCreationHelper;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFSimpleShape;
import org.apache.poi.hssf.usermodel.HSSFTextbox;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;

public class T3_Cell extends AbstractSample {

	private static String FILE_DIR = "C:\\Users\\Administrator\\Desktop\\";
	
	private static final String TEST_CELL_NAME = FILE_DIR + "sample_cell.xls";

	public static void main(String[] args) {
		
		File f = new File(FILE_DIR);
		if (!f.isDirectory()) {
			f.mkdirs();
		}
		
		T3_Cell c = new T3_Cell();
		c.createSheet();

		//设置单元格值
		c.setCellValue();
		
		//设置单元格边线
		c.setCellBorder();
		
		//设置单元格背景填充
		c.setCellFill();
		
		//设置单元格注释
		c.setCellComment();
		
		//设置单元格字体（斜体，粗体，下线，取消线，字体，大小，背景色）
		c.setCellFont();
		
		//设置超链接
		c.setCellHyperlink();
		
		//设置单元格横向对齐，纵向对齐
		c.setCellAlignment();
		
		//设置单元格旋转角度
		c.setCellRotation();
		
		//设置单元格自动折行
		c.setCellWrapText();
		
		//设置单元格文字缩进
		c.setCellIndent();
		
		//自定义格式
		c.setCellDataFormat();
		
		//设置公式
		c.setCellFunction();
		
		//画直线，圆圈（椭圆），正方形（长方形）,Textbox
		c.drawShape();
		
		//插入图片
		c.insertImage();
		
		//设置可输入List
		c.setCellValueList();
		
		//设置输入提示信息
		c.setCellInputTitle();
	}

	private void createSheet() {
		HSSFWorkbook wb = new HSSFWorkbook();
		for (int i = 0; i < 20; i++) {
			wb.createSheet("sheetname" + (i+1));
		}		
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellValue() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
				
		//追加行
		Row[] rows = new Row[10];
	    for (int i = 0; i < 10; i++) {
	    	rows[i] = wb.getSheet("sheetname1").createRow(i);
	    }
	    
	    //boolean
        Cell cell00 = rows[0].createCell(0);
        boolean val00 = true;
        cell00.setCellValue(val00);
        
        //Calendar 格式化
        CellStyle styleCalendar = wb.createCellStyle();
        DataFormat formatCalendar = wb.createDataFormat();
        styleCalendar.setDataFormat(formatCalendar.getFormat("yyyy/mm/dd"));
        Cell cell11 = rows[1].createCell(0);
        Calendar val11 = Calendar.getInstance();
        cell11.setCellStyle(styleCalendar);
        cell11.setCellValue(val11);

        //Date 格式化
        CellStyle styleDate = wb.createCellStyle();
        DataFormat formatDate = wb.createDataFormat();
        styleDate.setDataFormat(formatDate.getFormat("yyyy/mm/dd hh:mm"));
        Cell cell21 = rows[2].createCell(0);
        Date val21 = new Date();
        cell21.setCellStyle(styleDate);
        cell21.setCellValue(val21);

        //double
        Cell cell30 = rows[3].createCell(0);
        double val30 = 1234.56;
        cell30.setCellValue(val30);

        //double 格式化
        CellStyle styleDouble = wb.createCellStyle();
        DataFormat formatDouble = wb.createDataFormat();
        styleDouble.setDataFormat(formatDouble.getFormat("#,##0.00"));
        Cell cell31 = rows[3].createCell(1);
        double val31 = 1234.56;
        cell31.setCellStyle(styleDouble);
        cell31.setCellValue(val31);

        //String
        Cell cell40 = rows[4].createCell(0);
        HSSFRichTextString val40 = new HSSFRichTextString("Test汉字");
        cell40.setCellValue(val40);

		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellBorder() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		wb.getSheet("sheetname2").setColumnWidth(1, 4096);
		
		Row row1 = wb.getSheet("sheetname2").createRow(1);
	    row1.setHeightInPoints(70);

	    Cell cell1_1 = row1.createCell(1);
	    cell1_1.setCellValue("Sample");

	    CellStyle style = wb.createCellStyle();

	    style.setBorderTop(CellStyle.BORDER_DASHED);
	    style.setBorderBottom(CellStyle.BORDER_DOUBLE);
	    style.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT);
	    style.setBorderRight(CellStyle.BORDER_MEDIUM);

	    style.setTopBorderColor(IndexedColors.MAROON.getIndex());
	    style.setBottomBorderColor(IndexedColors.SKY_BLUE.getIndex());
	    style.setLeftBorderColor(IndexedColors.ORANGE.getIndex());
	    style.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());

	    cell1_1.setCellStyle(style);
		
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellFill() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		wb.getSheet("sheetname3").setColumnWidth(0, 4096);
		wb.getSheet("sheetname3").setColumnWidth(1, 4096);
		wb.getSheet("sheetname3").setColumnWidth(2, 4096);

	    Row row1 = wb.getSheet("sheetname3").createRow(1);
	    row1.setHeightInPoints(70);

	    Cell cell1_0 = row1.createCell(0);
	    Cell cell1_1 = row1.createCell(1);
	    Cell cell1_2 = row1.createCell(2);

	    cell1_0.setCellValue("THIN_VERT_BANDS");
	    cell1_1.setCellValue("BIG_SPOTS");
	    cell1_2.setCellValue("THICK_HORZ_BANDS");

	    CellStyle style1 = wb.createCellStyle();
	    style1.setFillPattern(CellStyle.THIN_VERT_BANDS);
	    style1.setFillForegroundColor(IndexedColors.WHITE.getIndex());
	    style1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());

	    CellStyle style2 = wb.createCellStyle();
	    style2.setFillPattern(CellStyle.BIG_SPOTS);
	    style2.setFillForegroundColor(IndexedColors.RED.getIndex());
	    style2.setFillBackgroundColor(IndexedColors.WHITE.getIndex());

	    CellStyle style3 = wb.createCellStyle();
	    style3.setFillPattern(CellStyle.THICK_HORZ_BANDS);
	    style3.setFillForegroundColor(IndexedColors.PINK.getIndex());
	    style3.setFillBackgroundColor(IndexedColors.BROWN.getIndex());

	    cell1_0.setCellStyle(style1);
	    cell1_1.setCellStyle(style2);
	    cell1_2.setCellStyle(style3);
	    
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellComment() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
        HSSFCreationHelper createHelper =
            (HSSFCreationHelper)wb.getCreationHelper();
        Drawing patriarch = wb.getSheet("sheetname4").createDrawingPatriarch();
        
        //注释
        Row row = wb.getSheet("sheetname4").createRow(1);
        Cell cell = row.createCell(1);

        HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 0, 0, 0,
				(short) 4, 2, (short) 6, 5);

        Comment comment = patriarch.createCellComment(clientAnchor);
        comment.setString(createHelper.createRichTextString("注释注释111"));
        comment.setAuthor("rensanning");

        cell.setCellComment(comment);
        
        //带字体的注释
        Row row2 = wb.getSheet("sheetname4").createRow(2);
        Cell cell2 = row2.createCell(1);

        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)10);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(HSSFColor.RED.index);

        Comment comment2 = patriarch.createCellComment(clientAnchor);
        HSSFRichTextString text = new HSSFRichTextString("注释注释222");
        text.applyFont(font);
        comment2.setString(text);
        comment2.setAuthor("rensanning");

        cell2.setCellComment(comment2);
        
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellFont() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		//追加行
	    for (int i = 0; i < 20; i++) {
		    Row row = wb.getSheet("sheetname5").createRow(i);
		    for (int j = 0; j < 10; j++) {
			    Cell cell = row.createCell(j);
			    cell.setCellValue("文字" + i + 1);
		    }
	    }
	    
	    Font font = null;
	    CellStyle style = null;
	    
	    //斜体
        font = wb.createFont();
        font.setItalic(true);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(1).getCell(1).setCellStyle(style);
        
        //粗体
        font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(2).getCell(1).setCellStyle(style);
        
        //字体名
        font = wb.createFont();
        font.setFontName("Courier New");
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(3).getCell(1).setCellStyle(style);
        
        //字体大小
        font = wb.createFont();
        font.setFontHeightInPoints((short)20);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(4).getCell(1).setCellStyle(style);
        
        //文字颜色
        font = wb.createFont();
        font.setColor(HSSFColor.YELLOW.index);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(5).getCell(1).setCellStyle(style);
                
        //上标
        font = wb.createFont();
        font.setTypeOffset(HSSFFont.SS_SUPER);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(6).getCell(1).setCellStyle(style);
        
        //下标
        font = wb.createFont();
        font.setTypeOffset(HSSFFont.SS_SUB);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(7).getCell(1).setCellStyle(style);
        
        //删除线
        font = wb.createFont();
        font.setStrikeout(true);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(8).getCell(1).setCellStyle(style);
        
        //下划线
        font = wb.createFont();
        font.setUnderline(HSSFFont.U_SINGLE);
        style = wb.createCellStyle();
        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(9).getCell(1).setCellStyle(style);
        
        //背景色
        style = wb.createCellStyle();
        style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        style.setFont(font);
        
        wb.getSheet("sheetname5").getRow(10).getCell(1).setCellStyle(style);
        
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellHyperlink() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		HSSFCreationHelper createHelper =
            (HSSFCreationHelper)wb.getCreationHelper();

        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setUnderline(HSSFFont.U_SINGLE);
        font.setColor(HSSFColor.BLUE.index);
        style.setFont(font);
        
		//追加行
		Row[] rows = new Row[10];
	    for (int i = 0; i < 10; i++) {
	    	rows[i] = wb.getSheet("sheetname6").createRow(i);
	    }

        //URL
        rows[0].createCell(0).setCellValue("URL Link");
        
        HSSFHyperlink link1 = createHelper.createHyperlink(HSSFHyperlink.LINK_URL);
        link1.setAddress("http://poi.apache.org/");
        rows[0].getCell(0).setHyperlink(link1);
        rows[0].getCell(0).setCellStyle(style);
        
        //Mail
        rows[1].createCell(0).setCellValue("Email Link");
        
        HSSFHyperlink link2 = createHelper.createHyperlink(HSSFHyperlink.LINK_EMAIL);
        link2.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
        rows[1].getCell(0).setHyperlink(link2);
        rows[1].getCell(0).setCellStyle(style);
        
        //File
        rows[2].createCell(0).setCellValue("File Link");
        
        HSSFHyperlink link3 = createHelper.createHyperlink(HSSFHyperlink.LINK_FILE);
        link3.setAddress("link.xls");
        rows[2].getCell(0).setHyperlink(link3);
        rows[2].getCell(0).setCellStyle(style);
        
        //Workbook内
        rows[3].createCell(0).setCellValue("Worksheet Link");
        
        HSSFHyperlink link4 = createHelper.createHyperlink(HSSFHyperlink.LINK_DOCUMENT);
        link4.setAddress("sheetname1!A1");
        rows[3].getCell(0).setHyperlink(link4);
        rows[3].getCell(0).setCellStyle(style);
        
		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void setCellAlignment() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		//横向对齐
		wb.getSheet("sheetname7").setColumnWidth(2, 3072);

	    Row[] row = new Row[7];
	    Cell[] cell = new Cell[7];

	    for (int i = 0 ; i < 7 ; i++){
	      row[i] = wb.getSheet("sheetname7").createRow(i + 1);
	      cell[i] = row[i].createCell(2);
	      cell[i].setCellValue("Please give me a receipt");
	    }

	    CellStyle style0 = wb.createCellStyle();
	    style0.setAlignment(CellStyle.ALIGN_GENERAL);
	    cell[0].setCellStyle(style0);

	    CellStyle style1 = wb.createCellStyle();
	    style1.setAlignment(CellStyle.ALIGN_LEFT);
	    cell[1].setCellStyle(style1);

	    CellStyle style2 = wb.createCellStyle();
	    style2.setAlignment(CellStyle.ALIGN_CENTER);
	    cell[2].setCellStyle(style2);

	    CellStyle style3 = wb.createCellStyle();
	    style3.setAlignment(CellStyle.ALIGN_RIGHT);
	    cell[3].setCellStyle(style3);

	    CellStyle style4 = wb.createCellStyle();
	    style4.setAlignment(CellStyle.ALIGN_FILL);
	    cell[4].setCellStyle(style4);

	    CellStyle style5 = wb.createCellStyle();
	    style5.setAlignment(CellStyle.ALIGN_JUSTIFY);
	    cell[5].setCellStyle(style5);

	    CellStyle style6 = wb.createCellStyle();
	    style6.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
	    cell[6].setCellStyle(style6);
	    
	    //纵向对齐
	    Row row2 = wb.getSheet("sheetname8").createRow(1);
	    row2.setHeightInPoints(70);
	    Cell[] cell2 = new Cell[4];

	    for (int i = 0 ; i < 4 ; i++){
	    	cell2[i] = row2.createCell(i + 1);
	    	cell2[i].setCellValue("Please give me a receipt");
	    }

	    CellStyle style02 = wb.createCellStyle();
	    style02.setVerticalAlignment(CellStyle.VERTICAL_TOP);
	    cell2[0].setCellStyle(style02);

	    CellStyle style12 = wb.createCellStyle();
	    style12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	    cell2[1].setCellStyle(style12);

	    CellStyle style22 = wb.createCellStyle();
	    style22.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
	    cell2[2].setCellStyle(style22);

	    CellStyle style32 = wb.createCellStyle();
	    style32.setVerticalAlignment(CellStyle.VERTICAL_JUSTIFY);
	    cell2[3].setCellStyle(style32);

		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void setCellRotation() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);

	    Row[] row = new Row[4];
	    Cell[] cell = new Cell[4];

	    for (int i = 0 ; i < 4 ; i++){
	      row[i] = wb.getSheet("sheetname9").createRow(i + 1);
	      cell[i] = row[i].createCell(2);
	      cell[i].setCellValue("Coffee");
	    }

	    CellStyle style0 = wb.createCellStyle();
	    style0.setRotation((short)45);
	    cell[0].setCellStyle(style0);

	    CellStyle style1 = wb.createCellStyle();
	    style1.setRotation((short)0);
	    cell[1].setCellStyle(style1);

	    CellStyle style2 = wb.createCellStyle();
	    style2.setRotation((short)-45);
	    cell[2].setCellStyle(style2);

	    CellStyle style3 = wb.createCellStyle();
	    style3.setRotation((short)-90);
	    cell[3].setCellStyle(style3);
	    
		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void setCellWrapText() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		Row[] row = new Row[2];
	    Cell[] cell = new Cell[2];

	    for (int i = 0 ; i < 2 ; i++){
	      row[i] = wb.getSheet("sheetname10").createRow(i + 1);
	      cell[i] = row[i].createCell(2);
	      cell[i].setCellValue("Thank you very much.");
	    }

	    CellStyle style0 = wb.createCellStyle();
	    style0.setWrapText(true);
	    cell[0].setCellStyle(style0);

	    CellStyle style1 = wb.createCellStyle();
	    style1.setWrapText(false);
	    cell[1].setCellStyle(style1);

		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void setCellIndent() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);

		Row[] row = new Row[4];
	    Cell[] cell = new Cell[4];

	    for (int i = 0 ; i < 4 ; i++){
	      row[i] = wb.getSheet("sheetname11").createRow(i + 1);
	      cell[i] = row[i].createCell(2);
	      cell[i].setCellValue("Coffee");
	    }

	    CellStyle style1 = wb.createCellStyle();
	    style1.setIndention((short)1);
	    style1.setAlignment(CellStyle.ALIGN_LEFT);
	    cell[1].setCellStyle(style1);

	    CellStyle style2 = wb.createCellStyle();
	    style2.setIndention((short)2);
	    style2.setAlignment(CellStyle.ALIGN_LEFT);
	    cell[2].setCellStyle(style2);

	    CellStyle style3 = wb.createCellStyle();
	    style3.setIndention((short)3);
	    style3.setAlignment(CellStyle.ALIGN_LEFT);
	    cell[3].setCellStyle(style3);

		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void setCellDataFormat() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
        Row[] rows = new Row[2];
        for (int i = 0; i < rows.length; i++) {
            rows[i] = wb.getSheet("sheetname12").createRow(i + 1);
        }
        DataFormat format = wb.createDataFormat();
        
        CellStyle[] styles = new CellStyle[2];
        for (int i = 0; i < styles.length; i++) {
            styles[i] = wb.createCellStyle();
        }
        styles[0].setDataFormat(format.getFormat("0.0"));
        styles[1].setDataFormat(format.getFormat("#,##0.000"));

        Cell[] cells = new Cell[2];
        for (int i = 0; i < cells.length; i++)  {
            cells[i] = rows[i].createCell(1);
            cells[i].setCellValue(1111.25);

            cells[i].setCellStyle(styles[i]);
        }
        
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellFunction() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		Row row1 = wb.getSheet("sheetname13").createRow(1);
	    Row row2 = wb.getSheet("sheetname13").createRow(2);

	    Cell cell1_1 = row1.createCell(1);
	    Cell cell1_2 = row1.createCell(2);
	    Cell cell1_3 = row1.createCell(3);
	    Cell cell2_3 = row2.createCell(3);

	    cell1_1.setCellValue(30);
	    cell1_2.setCellValue(25);
	    cell1_3.setCellFormula("B2+C2");
	    cell2_3.setCellFormula("MOD(B2,C2)");
        
		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void drawShape() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
        HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname14")).createDrawingPatriarch();
		
		//直线
        HSSFClientAnchor clientAnchor1 = new HSSFClientAnchor(0, 0, 0, 0,
				(short) 4, 2, (short) 6, 5);
        HSSFSimpleShape shape1 = patriarch.createSimpleShape(clientAnchor1);
        shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
        
		//圆圈（椭圆）
        HSSFClientAnchor clientAnchor2 = new HSSFClientAnchor(0, 0, 0, 0,
				(short) 8, 4, (short) 6, 5);
        HSSFSimpleShape shape2 = patriarch.createSimpleShape(clientAnchor2);
        shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);

		//正方形（长方形）
        HSSFClientAnchor clientAnchor3 = new HSSFClientAnchor(0, 0, 0, 0,
				(short) 12, 6, (short) 6, 5);
        HSSFSimpleShape shape3 = patriarch.createSimpleShape(clientAnchor3);
        shape3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
        
        //Textbox
        HSSFClientAnchor clientAnchor4 = new HSSFClientAnchor(0, 0, 0, 0,
				(short) 14, 8, (short) 6, 5);
        HSSFTextbox textbox = patriarch.createTextbox(clientAnchor4);
        textbox.setString(new HSSFRichTextString("This is a test"));
        
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void insertImage() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
		try {
			
			//需要commons-codec-1.6.jar
			FileInputStream jpeg = new FileInputStream("resource/test.jpg");
	        byte[] bytes = IOUtils.toByteArray(jpeg);
	        int pictureIndex = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
	        jpeg.close();
	
	        HSSFCreationHelper helper = (HSSFCreationHelper) wb.getCreationHelper();
	
	        HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname15")).createDrawingPatriarch();
	
	        HSSFClientAnchor clientAnchor = helper.createClientAnchor();
	
	        clientAnchor.setCol1(3);
	        clientAnchor.setRow1(2);
	
	        HSSFPicture picture = patriarch.createPicture(clientAnchor, pictureIndex);
	        picture.resize();
		} catch (Exception e) {
			e.printStackTrace();
		}
        
		createExcelFile(wb, TEST_CELL_NAME);
	}
	
	private void setCellValueList() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
        CellRangeAddressList addressList = new CellRangeAddressList(
                0,
                0,
                0,
                0);

        final String[] DATA_LIST = new String[] {
                "10",
                "20",
                "30",
        };
        DVConstraint dvConstraint =
            DVConstraint.createExplicitListConstraint(DATA_LIST);

        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);

        wb.getSheet("sheetname16").addValidationData(dataValidation);
        
		createExcelFile(wb, TEST_CELL_NAME);
	}

	private void setCellInputTitle() {
		Workbook wb = readExcelFile(TEST_CELL_NAME);
		
        CellRangeAddressList addressList = new CellRangeAddressList(
                0,
                0,
                0,
                0);

        final String[] DATA_LIST = new String[] {
                "10",
                "20",
                "30",
        };
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(DATA_LIST);

        HSSFDataValidation dataValidation =
            new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "请从下拉列表中选择！");
        dataValidation.setShowPromptBox(true);

        wb.getSheet("sheetname17").addValidationData(dataValidation);
        
		createExcelFile(wb, TEST_CELL_NAME);
	}
}
